package com.system.comm.utils;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.system.comm.utils.excel.ExcelCell;
import com.system.comm.utils.excel.ExcelSheet;
import com.system.comm.utils.excel.ExcelTitle;

/**
 * Excel的操作
 * @author yuejing
 * @date 2019年11月27日 下午3:24:24
 */
public class FrameExcelUtil {

	/**
	 * 创建Excel
	 * @param excelSheet
	 * @param savePath
	 */
	public static void createExcel(ExcelSheet excelSheet, String savePath) {
		List<ExcelSheet> sheetList = new ArrayList<>();
		sheetList.add(excelSheet);
		createExcel(sheetList, savePath);
	}
	/**
	 * 创建Excel
	 * @param sheetList
	 * @param savePath
	 */
	public static void createExcel(List<ExcelSheet> sheetList, String savePath) {
		// 第一步，创建一个webbook，对应一个Excel文件
		HSSFWorkbook wb = new HSSFWorkbook();
		for (ExcelSheet excelSheet : sheetList) {
			int rowNum = 0;
			// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
			if (FrameStringUtil.isEmpty(excelSheet.getName())) {
				excelSheet.setName("sheet1");
			}
			HSSFSheet sheet = wb.createSheet(excelSheet.getName());
			// 默认列宽
			sheet.setDefaultColumnWidth(20);
			HSSFRow row = null;
			List<ExcelTitle> excelTitles = excelSheet.getTitleList();
			for (ExcelTitle excelTitle : excelTitles) {
				// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
				row = sheet.createRow(rowNum);
				if (excelTitle.getHeight() != null) {
					row.setHeightInPoints(excelTitle.getHeight());
				}
				List<ExcelCell> titleList = excelTitle.getTitles();

				HSSFCell cell = null;
				// 添加excel title
				int cellNum = 0;
				for (ExcelCell excelCell : titleList) {
					// 第四步，创建单元格，并设置值表头 设置表头居中
					HSSFCellStyle style = wb.createCellStyle();
					if (excelCell.getAlign() != null) {
						style.setAlignment(excelCell.getAlign());
					} else {
						// 创建一个居中格式
						style.setAlignment(HorizontalAlignment.CENTER);
					}
					style.setFillForegroundColor(excelTitle.getBackgroundColor().getIndex());
					style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
					style.setBorderBottom(BorderStyle.THIN);
					style.setBorderRight(BorderStyle.THIN);
					style.setVerticalAlignment(VerticalAlignment.CENTER);
					HSSFFont font = wb.createFont();
					if (excelCell.getFontSize() != null) {
						//设置字体大小
						font.setFontHeightInPoints(excelCell.getFontSize());
					}
					if (excelCell.getBold() != null) {
						font.setBold(excelCell.getBold());
					}
					style.setFont(font);

					cell = row.createCell(cellNum);
					if (excelCell.getValue() != null) {
						cell.setCellValue(excelCell.getValue().toString());
					} else {
						cell.setCellValue("");
					}
					cell.setCellStyle(style);
					if (excelCell.getColSpan() > 1) {
						sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, cellNum, cellNum + excelCell.getColSpan() - 1));
						for (int i = 0; i < excelCell.getColSpan() - 1; i++) {
							cellNum ++;
							cell = row.createCell(cellNum);
							cell.setCellStyle(style);
						}
					}
					cellNum ++;
				}
				rowNum ++;
			}
			HSSFCellStyle leftStyle = wb.createCellStyle();
			leftStyle.setAlignment(HorizontalAlignment.LEFT);
			leftStyle.setBorderBottom(BorderStyle.THIN);
			leftStyle.setBorderRight(BorderStyle.THIN);
			HSSFDataFormat format = wb.createDataFormat();
			leftStyle.setDataFormat(format.getFormat("###,###,###,###,###,###,###,###,###,###,##0.00###################"));

			HSSFCellStyle rightStyle = wb.createCellStyle();
			rightStyle.setAlignment(HorizontalAlignment.RIGHT);
			rightStyle.setBorderBottom(BorderStyle.THIN);
			rightStyle.setBorderRight(BorderStyle.THIN);
			rightStyle.setDataFormat(format.getFormat("###,###,###,###,###,###,###,###,###,###,##0.00###################"));

			HSSFCellStyle centerStyle = wb.createCellStyle();
			centerStyle.setAlignment(HorizontalAlignment.CENTER);
			centerStyle.setBorderBottom(BorderStyle.THIN);
			centerStyle.setBorderRight(BorderStyle.THIN);
			centerStyle.setDataFormat(format.getFormat("###,###,###,###,###,###,###,###,###,###,##0.00###################"));

			List<List<ExcelCell>> valueList = excelSheet.getValueList();
			// 第五步，写入实体数据 实际应用中这些数据从数据库得到,list中字符串的顺序必须和数组strArray中的顺序一致
			for (List<ExcelCell> values : valueList) {
				row = sheet.createRow(rowNum);
				rowNum ++;

				// 第四步，创建单元格，并设置值
				for (int j = 0; j < values.size(); j++) {
					if (values.size() <= j) {
						continue;
					}
					ExcelCell excelCell = values.get(j);
					HSSFCell cell = row.createCell((short) j);
					if (excelCell.getValue() != null) {
						if (excelCell.getValue() instanceof String) {
							cell.setCellValue(excelCell.getValue().toString());
						} else if (excelCell.getValue() instanceof Double) {
							cell.setCellValue((Double) excelCell.getValue());
						} else if (excelCell.getValue() instanceof Integer) {
							cell.setCellValue((Integer) excelCell.getValue());
						}
					}
					if (excelCell.getAlign() == null || excelCell.getAlign() == HorizontalAlignment.LEFT) {
						cell.setCellStyle(leftStyle);
					} else if (excelCell.getAlign() == HorizontalAlignment.RIGHT) {
						cell.setCellStyle(rightStyle);
					} else if (excelCell.getAlign() == HorizontalAlignment.CENTER) {
						cell.setCellStyle(centerStyle);
					}

					if (excelCell.getCellType() != null && excelCell.getValue() != null) {
						cell.setCellType(excelCell.getCellType());
					} else {
						if (excelCell.getValue() == null) {
							cell.setCellValue("");
						}
						cell.setCellType(CellType.STRING);
					}
				}

			}
			FileOutputStream fout = null;
			// 第六步，将文件存到指定位置
			try {
				fout = new FileOutputStream(savePath);
				wb.write(fout);
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					if (fout != null) {
						fout.close();
					}
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		try {
			wb.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 创建Excel 07版本
	 * @param sheetList
	 * @param savePath
	 */
	public static void createExcel07(List<ExcelSheet> sheetList, String savePath) {
		// 第一步，创建一个webbook，对应一个Excel文件
		XSSFWorkbook wb = new XSSFWorkbook();
		for (ExcelSheet excelSheet : sheetList) {
			int rowNum = 0;
			// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
			if (FrameStringUtil.isEmpty(excelSheet.getName())) {
				excelSheet.setName("sheet1");
			}
			XSSFSheet sheet = wb.createSheet(excelSheet.getName());
			// 默认列宽
			sheet.setDefaultColumnWidth(20);
			XSSFRow row = null;
			List<ExcelTitle> excelTitles = excelSheet.getTitleList();
			for (ExcelTitle excelTitle : excelTitles) {
				// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
				row = sheet.createRow(rowNum);
				if (excelTitle.getHeight() != null) {
					row.setHeightInPoints(excelTitle.getHeight());
				}
				List<ExcelCell> titleList = excelTitle.getTitles();

				XSSFCell cell = null;
				// 添加excel title
				int cellNum = 0;
				for (ExcelCell excelCell : titleList) {
					// 第四步，创建单元格，并设置值表头 设置表头居中
					XSSFCellStyle style = wb.createCellStyle();
					if (excelCell.getAlign() != null) {
						style.setAlignment(excelCell.getAlign());
					} else {
						// 创建一个居中格式
						style.setAlignment(HorizontalAlignment.CENTER);
					}
					style.setFillForegroundColor(excelTitle.getBackgroundColor().getIndex());
					style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
					style.setBorderBottom(BorderStyle.THIN);
					style.setBorderRight(BorderStyle.THIN);
					style.setVerticalAlignment(VerticalAlignment.CENTER);
					XSSFFont font = wb.createFont();
					if (excelCell.getFontSize() != null) {
						//设置字体大小
						font.setFontHeightInPoints(excelCell.getFontSize());
					}
					if (excelCell.getBold() != null) {
						font.setBold(excelCell.getBold());
					}
					style.setFont(font);

					cell = row.createCell(cellNum);
					if (excelCell.getValue() != null) {
						cell.setCellValue(excelCell.getValue().toString());
					} else {
						cell.setCellValue("");
					}
					cell.setCellStyle(style);
					if (excelCell.getColSpan() > 1) {
						sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, cellNum, cellNum + excelCell.getColSpan() - 1));
						for (int i = 0; i < excelCell.getColSpan() - 1; i++) {
							cellNum ++;
							cell = row.createCell(cellNum);
							cell.setCellStyle(style);
						}
					}
					cellNum ++;
				}
				rowNum ++;
			}
			XSSFCellStyle leftStyle = wb.createCellStyle();
			leftStyle.setAlignment(HorizontalAlignment.LEFT);
			leftStyle.setBorderBottom(BorderStyle.THIN);
			leftStyle.setBorderRight(BorderStyle.THIN);
			XSSFDataFormat format = wb.createDataFormat();
			leftStyle.setDataFormat(format.getFormat("###,###,###,###,###,###,###,###,###,###,##0.00###################"));

			XSSFCellStyle rightStyle = wb.createCellStyle();
			rightStyle.setAlignment(HorizontalAlignment.RIGHT);
			rightStyle.setBorderBottom(BorderStyle.THIN);
			rightStyle.setBorderRight(BorderStyle.THIN);
			rightStyle.setDataFormat(format.getFormat("###,###,###,###,###,###,###,###,###,###,##0.00###################"));

			XSSFCellStyle centerStyle = wb.createCellStyle();
			centerStyle.setAlignment(HorizontalAlignment.CENTER);
			centerStyle.setBorderBottom(BorderStyle.THIN);
			centerStyle.setBorderRight(BorderStyle.THIN);
			centerStyle.setDataFormat(format.getFormat("###,###,###,###,###,###,###,###,###,###,##0.00###################"));

			List<List<ExcelCell>> valueList = excelSheet.getValueList();
			// 第五步，写入实体数据 实际应用中这些数据从数据库得到,list中字符串的顺序必须和数组strArray中的顺序一致
			for (List<ExcelCell> values : valueList) {
				row = sheet.createRow(rowNum);
				rowNum ++;

				// 第四步，创建单元格，并设置值
				for (int j = 0; j < values.size(); j++) {
					if (values.size() <= j) {
						continue;
					}
					ExcelCell excelCell = values.get(j);
					XSSFCell cell = row.createCell((short) j);
					if (excelCell.getValue() != null) {
						if (excelCell.getValue() instanceof String) {
							cell.setCellValue(excelCell.getValue().toString());
						} else if (excelCell.getValue() instanceof Double) {
							cell.setCellValue((Double) excelCell.getValue());
						} else if (excelCell.getValue() instanceof Integer) {
							cell.setCellValue((Integer) excelCell.getValue());
						}
					}
					if (excelCell.getAlign() == null || excelCell.getAlign() == HorizontalAlignment.LEFT) {
						cell.setCellStyle(leftStyle);
					} else if (excelCell.getAlign() == HorizontalAlignment.RIGHT) {
						cell.setCellStyle(rightStyle);
					} else if (excelCell.getAlign() == HorizontalAlignment.CENTER) {
						cell.setCellStyle(centerStyle);
					}

					if (excelCell.getCellType() != null && excelCell.getValue() != null) {
						cell.setCellType(excelCell.getCellType());
					} else {
						if (excelCell.getValue() == null) {
							cell.setCellValue("");
						}
						cell.setCellType(CellType.STRING);
					}
				}

			}
			FileOutputStream fout = null;
			// 第六步，将文件存到指定位置
			try {
				fout = new FileOutputStream(savePath);
				wb.write(fout);
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					if (fout != null) {
						fout.close();
					}
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		try {
			wb.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 读取Excel的数据(2003版本)
	 * @param dataPath
	 * @param topRow	标题行数
	 * @return
	 * @throws IOException
	 */
	public static List<Map<String, String>> readData(String dataPath, int topRow) throws IOException {
		List<Map<String, String>> result = null;
		if(dataPath.endsWith(".xls")) {
			result = readDataXls(dataPath, topRow);
		} else {
			result = readDataXlsx(dataPath, topRow);
		}
		return result;
	}

	private static List<Map<String, String>> readDataXls(String dataPath, int topRow) throws IOException {
		List<Map<String, String>> result = new ArrayList<Map<String, String>>();
		InputStream is = new FileInputStream(dataPath);

		HSSFWorkbook wb = new HSSFWorkbook(is);
		// 获取第一个sheet表
		HSSFSheet sheet = wb.getSheetAt(0);
		HSSFRow rowData;

		for (int i = sheet.getTopRow(); i < sheet.getLastRowNum() + 1; i++) {
			if(i < topRow) {
				continue;
			}
			rowData = sheet.getRow(i);
			/*if (i == sheet.getTopRow()) {
				continue; //标题不读入
			}*/

			Map<String, String> map = new HashMap<String, String>();

			for (int j = rowData.getFirstCellNum(); j < rowData.getLastCellNum(); j++) {
				if(rowData.getCell(j) == null) {
					continue;
				}
				/*if (j == rowData.getFirstCellNum()) {
					trans_key = rowData.getCell(j).toString();
				} else {
					map.put(j, rowData.getCell(j).toString());
				}*/
				Cell cell = rowData.getCell(j);
				String value = null;
				if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
					if(DateUtil.isCellDateFormatted(cell)) {
						Date date = cell.getDateCellValue();
						value = FrameTimeUtil.parseString(date, FrameTimeUtil.FMT_YYYY_MM_DD);
					} else {
						value = cell.toString();
					}
				} else {
					value = cell.toString();
				}
				/*if(value.startsWith("=VLOOKUP(A2,'") && value.endsWith("[【先智总确认版】产品-客户-归属部门（基于存续产品）(1221)二部反馈.xlsx]最终结果'!$A$2:$E$467,5,0)")) {
					value = "金融机构一部";
				}*/
				map.put(String.valueOf(j), value);
			}
			result.add(map);
		}
		is.close();
		return result;
	}

	private static List<Map<String, String>> readDataXlsx(String dataPath, int topRow) throws IOException {
		List<Map<String, String>> result = new ArrayList<Map<String, String>>();
		InputStream is = new FileInputStream(dataPath);

		XSSFWorkbook wb = new XSSFWorkbook(is);
		// 获取第一个sheet表
		XSSFSheet sheet = wb.getSheetAt(0);
		XSSFRow rowData;

		for (int i = sheet.getTopRow(); i < sheet.getLastRowNum() + 1; i++) {
			if(i < topRow) {
				continue;
			}
			rowData = sheet.getRow(i);
			/*if (i == sheet.getTopRow()) {
				continue; //标题不读入
			}*/

			if (rowData == null) {
				continue;
			}

			Map<String, String> map = new HashMap<String, String>();

			for (int j = rowData.getFirstCellNum(); j < rowData.getLastCellNum(); j++) {
				if(rowData.getCell(j) == null) {
					continue;
				}
				/*if (j == rowData.getFirstCellNum()) {
					trans_key = rowData.getCell(j).toString();
				} else {
					map.put(j, rowData.getCell(j).toString());
				}*/
				Cell cell = rowData.getCell(j);
				String value = null;
				if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
					if(DateUtil.isCellDateFormatted(cell)) {
						Date date = cell.getDateCellValue();
						value = FrameTimeUtil.parseString(date, FrameTimeUtil.FMT_YYYY_MM_DD);
					} else {
						value = cell.toString();
					}
				} else {
					value = cell.toString();
				}
				/*if(value.startsWith("=VLOOKUP(A2,'") && value.endsWith("[【先智总确认版】产品-客户-归属部门（基于存续产品）(1221)二部反馈.xlsx]最终结果'!$A$2:$E$467,5,0)")) {
					value = "金融机构一部";
				}*/
				map.put(String.valueOf(j), value);
			}
			result.add(map);
		}

		is.close();

		return result;
	}

	public static void main(String[] args) {
		String savePath = "D:\\data\\test.xlsx";
		List<ExcelTitle> titleList = new ArrayList<>();
		ExcelTitle excelTitle = new ExcelTitle();
		List<ExcelCell> titles = new ArrayList<>();
		titles.add(new ExcelCell("测试标题", 3, (short) 12, true));
		titles.add(new ExcelCell("2019-01-01"));
		excelTitle.setTitles(titles);
		excelTitle.setHeight(30f);
		excelTitle.setBackgroundColor(IndexedColors.GREY_25_PERCENT);
		titleList.add(excelTitle);
		excelTitle = new ExcelTitle();
		titles = new ArrayList<>();
		titles.add(new ExcelCell("111"));
		titles.add(new ExcelCell("222"));
		titles.add(new ExcelCell("333"));
		titles.add(new ExcelCell("444"));
		excelTitle.setTitles(titles);
		excelTitle.setBackgroundColor(IndexedColors.YELLOW);
		titleList.add(excelTitle);

		List<List<ExcelCell>> valueList = new ArrayList<>();
		List<ExcelCell> values = new ArrayList<>();
		values.add(new ExcelCell("eeee"));
		values.add(new ExcelCell(2017));
		values.add(new ExcelCell("2018"));
		values.add(new ExcelCell("2018"));
		valueList.add(values);
		values = new ArrayList<>();
		values.add(new ExcelCell("2020-01-01"));
		values.add(new ExcelCell(null, 1, CellType.NUMERIC));
		values.add(new ExcelCell(78909.34567, 1, HorizontalAlignment.RIGHT, CellType.NUMERIC));
		values.add(new ExcelCell(78909.34567, 1, HorizontalAlignment.RIGHT, CellType.NUMERIC));
		valueList.add(values);
		values = new ArrayList<>();
		values.add(new ExcelCell("q1"));
		values.add(new ExcelCell("w2"));
		values.add(new ExcelCell("e3"));
		values.add(new ExcelCell("e3"));
		valueList.add(values);

		List<ExcelSheet> sheets = new ArrayList<>();
		ExcelSheet excelSheet = new ExcelSheet("第一个sheet", titleList, valueList);
		sheets.add(excelSheet);
		createExcel07(sheets, savePath);
	}
}
